package cn.big.bigd.biocode.service;

import cn.big.bigd.biocode.dao.*;
import cn.big.bigd.biocode.dao.vocabulary.TechDAO;
import cn.big.bigd.biocode.dto.SearchDTO;
import cn.big.bigd.biocode.model.*;
import cn.big.bigd.biocode.model.vocabulary.Tech;
import cn.big.bigd.biocode.specification.CustomerSpecifications;
import cn.big.bigd.biocode.util.DateUtils;
import org.hibernate.SQLQuery;
import org.hibernate.transform.Transformers;
import org.slf4j.Logger;
import org.slf4j.LoggerFactory;
import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.data.domain.Page;
import org.springframework.data.domain.PageRequest;
import org.springframework.data.domain.Sort;
import org.springframework.stereotype.Service;
import org.springframework.transaction.annotation.EnableTransactionManagement;
import org.springframework.transaction.annotation.Propagation;
import org.springframework.transaction.annotation.Transactional;

import javax.annotation.PostConstruct;
import javax.persistence.EntityManager;
import javax.persistence.PersistenceContext;
import javax.persistence.Query;
import java.util.*;

/**
 * Created by wangfan on 2017/12/4.
 */
@Service
@EnableTransactionManagement(order = 1)
public class ToolService {
    private static final Logger logger = LoggerFactory.getLogger(ToolService.class);
    
    @PersistenceContext
    private EntityManager entityManager;
    private static ToolDAO toolDAO;
    @Autowired
    UserService userService;
    @Autowired
    UserDAO userDAO;
    @Autowired
    PublicationDAO publicationDAO;
    @Autowired
    ToolDAO toolDAO1;

    @Autowired
    OrganizationDAO organizationDAO;
    @Autowired
    TechDAO techDAO;

    private static VisitTrackDAO visitTrackDAO;
    @Autowired
    private VisitTrackDAO visitTrackDAO1;

    private static RateDAO rateDAO;
    @Autowired
    CreditDAO creditDAO;
    @Autowired
    private RateDAO rateDAO1;


    @PostConstruct
    public void initStaticDao () {
        visitTrackDAO = this.visitTrackDAO1;

        rateDAO= this.rateDAO1;

        toolDAO=this.toolDAO1;
    }

   /* public static long  calDownloadCount(Integer id){
        return null==visitTrackDAO.downloadToolCount(id)?0:visitTrackDAO.downloadToolCount(id);
    }*/
   public static double  calStarByUserAndToolAndDimension(Integer userId,Integer toolId,String dimension){
       return rateDAO.calStarByUserAndToolAndDimension(userId,toolId,dimension)==null?0.0:rateDAO.calStarByUserAndToolAndDimension(userId,toolId,dimension);
   }

    public static double  calAvgStarByRateableAndDimension(Integer toolId,String dimension,String type){
       double  result =0.0;
       if(rateDAO.countDistinctRateIdByRateableIdAndRateableType(toolId,type)>0){
           result =rateDAO.calSumStarByRateableAndDimensionAndType(toolId,dimension,type)/rateDAO.countDistinctRateIdByRateableIdAndRateableType(toolId,type);
       }
       return result;
    }

    public static String  calRateUserAndCreatedAt(Integer rID,Integer raId,String type){

        List<Object[]> list =rateDAO.calRateUserAndCreatedAt(rID,raId,type);
        if(list.size()>0){
            Object[]  o=list.get(0);
            if(null==o[0]){
                return  "";
            }
            String email =o[0].toString();
            if(o.length>1){
                if(null==o[1]){
                    return  email;
                }
                String d = DateUtils.format((Date)o[1]);

                return email+" ("+d+")";
            }
            return  email;

        }
      /*  if(o.length==1){
            String email =(String)o[0];
            return  email;
        }*/


        return  "";

    }
    public static int  countDistinctRateIdByRateableIdAndRateableType(Integer toolId,String type){
        return rateDAO.countDistinctRateIdByRateableIdAndRateableType(toolId,type);
    }

    public static int  calIdByUserAndToolAndDimension(Integer userId,Integer toolId,String dimension){
        return rateDAO.calIdByUserAndToolAndDimension(userId,toolId,dimension)==null?0:rateDAO.calIdByUserAndToolAndDimension(userId,toolId,dimension);
    }
    public static long  calToolDownloadCount(Integer id){
        return null==visitTrackDAO.toolDownloadCount(id)?0:visitTrackDAO.toolDownloadCount(id);
    }

    public static long  calReleaseDownloadCount(Integer id){
        if(null==id){
            return 0;
        }
        return null==visitTrackDAO.releaseDownloadCount(id)?0:visitTrackDAO.releaseDownloadCount(id);
    }

    public Date findCreateAt(Integer id){

        return toolDAO.findOne(id).getCreatedAt();
    }

    public List<Map<String, Object>> findPopularTools() {
        String sql="SELECT tools.id, avg(rates.stars) AS oavg" +
                "                            FROM tools\n" +
                "                              JOIN rates ON rates.rateable_type = 'Tool' AND rates.rateable_id = tools.id" +
                "                            GROUP BY rates.rateable_id" +
                "                            ORDER BY oavg DESC LIMIT 10";
        Query query=entityManager.createNativeQuery(sql);
        query.unwrap(SQLQuery.class).setResultTransformer(Transformers.ALIAS_TO_ENTITY_MAP);//设置按照数据库字段返回map 结果集
        List<Map<String, Object>> listRe= query.getResultList();

        for (Map<String, Object> stringObjectMap : listRe) {
            Integer toolId =(Integer) stringObjectMap.get("id");
            Tool t =toolDAO.findOne(toolId);
            stringObjectMap.put("tool",t);


        }
        return listRe;
    }




    public Page<Tool>  searchByCondition(SearchDTO s){
        Integer page=0;
        if(null==s.getPage()||s.getPage()<=1){
            page =0;
        }else if(s.getPage()>1){
            page=s.getPage()-1;
        }
        Sort sort = new Sort(Sort.Direction.ASC,"id");
        PageRequest pageReq = new PageRequest(page,s.getSize(), sort);
        Page<Tool> tools = toolDAO.findAll(CustomerSpecifications.searchSpec(s), pageReq);
        return tools;

    }
    public List<Map<String, Object>> findLatestTools() {
        String sql="SELECT releases.*,tools.name FROM releases INNER JOIN tools ON tools.id=releases.tool_id ORDER BY releases.created_at DESC ";
        Query query=entityManager.createNativeQuery(sql);
        query.unwrap(SQLQuery.class).setResultTransformer(Transformers.ALIAS_TO_ENTITY_MAP);//设置按照数据库字段返回map 结果集

        List<Map<String, Object>> listRe= query.getResultList();
        return listRe;
    }

    public static   Integer findContributedToolsByCreditRole(Integer uId, Integer croleId){

        return toolDAO.findContributedToolsByCreditRole(uId,croleId);

    }



    public List<Map<String, Object>> findTopUsers() {
        String sql="SELECT users.id,users.email, users.full_name,cc.co FROM users JOIN (SELECT user_id, count(*) AS co FROM credits GROUP BY user_id ORDER BY co DESC) cc ON cc.user_id=users.id LIMIT 15";
        Query query=entityManager.createNativeQuery(sql);
        query.unwrap(SQLQuery.class).setResultTransformer(Transformers.ALIAS_TO_ENTITY_MAP);//设置按照数据库字段返回map 结果集
        List<Map<String, Object>> listRe= query.getResultList();
        return listRe;
    }

    public List<Map<String, Object>> findTopOrgs() {
        String sql="SELECT organizations.name, count(*) AS co FROM credits, organizations WHERE organizations.id=credits.organization_id GROUP BY organizations.name ORDER BY co DESC LIMIT 15";
        Query query=entityManager.createNativeQuery(sql);
        query.unwrap(SQLQuery.class).setResultTransformer(Transformers.ALIAS_TO_ENTITY_MAP);//设置按照数据库字段返回map 结果集
        List<Map<String, Object>> listRe= query.getResultList();
        return listRe;
    }

    public List<Map<String, Object>> findToolDistribute() {
        String sql="SELECT  tools.country as code,count(*) as value  from tools WHERE  tools.country is not null GROUP BY tools.country ORDER BY value DESC;";
        Query query=entityManager.createNativeQuery(sql);
        query.unwrap(SQLQuery.class).setResultTransformer(Transformers.ALIAS_TO_ENTITY_MAP);//设置按照数据库字段返回map 结果集
        List<Map<String, Object>> listRe= query.getResultList();
        return listRe;
    }


    public List<Map<String, Object>> findMapListBySql(String sql ) {
        Query query=entityManager.createNativeQuery(sql);
        query.unwrap(SQLQuery.class).setResultTransformer(Transformers.ALIAS_TO_ENTITY_MAP);//设置按照数据库字段返回map 结果集
        List<Map<String, Object>> listRe= query.getResultList();
        return listRe;
    }
    public Tool findOne(Integer id){
       return toolDAO.findOne(id);
    }
    public void deleteOne(Integer id){
         toolDAO.delete(id);
    }


    @Transactional(propagation= Propagation.REQUIRES_NEW)
    public Tool save(Tool tool){
        List<Credit> credits =new ArrayList<>();
        List<Integer> vids =new ArrayList<>();
        List<Integer> cids =new ArrayList<>();
        List<Publications> publicationList =new ArrayList<>();
        if(null!=tool.getTeches()){
            for (int i = 0; i < tool.getTeches().size(); i++) {
                vids.add(tool.getTeches().get(i).getId());
            }
            tool.getTeches().clear();
        }

        if(null!=tool.getDataTypes()){
            for (int i = 0; i < tool.getDataTypes().size(); i++) {
                vids.add(tool.getDataTypes().get(i).getId());
            }
            tool.getDataTypes().clear();
        }

        if(null!=tool.getUserInterfaces()){
            for (int i = 0; i < tool.getUserInterfaces().size(); i++) {
                vids.add(tool.getUserInterfaces().get(i).getId());
            }
            tool.getUserInterfaces().clear();
        }

        if(null!=tool.getPlatforms()){
            for (int i = 0; i < tool.getPlatforms().size(); i++) {
                vids.add(tool.getPlatforms().get(i).getId());
            }

            tool.getPlatforms().clear();
        }
        if(null!=tool.getCategories()){
            for (int i = 0; i < tool.getCategories().size(); i++) {
                cids.add(tool.getCategories().get(i).getId());
            }

            tool.getCategories().clear();
        }
        if(null!=tool.getPublicationList()){
            publicationList.addAll(tool.getPublicationList());
            tool.getPublicationList().clear();

        }
        if(null!=tool.getCredits()){

           credits.addAll(tool.getCredits());
            System.out.println(credits.size());
            tool.getCredits().clear();

        }
        //清空credits

        if(tool.getId()!=null){
            System.out.println(tool.getId());
//            toolDAO.findOne(tool.getId());
            creditDAO.delete(toolDAO.findOne(tool.getId()).getCredits());
        }

        tool =toolDAO.save(tool);

      //除非你明确地发出了flush()指令，关于Session何时会执行这些JDBC调用是完全无法保证的，只能保证它们执行的前后顺序
        entityManager.flush();

        logger.info(vids.toString());
        for (Integer vid : vids) {
            toolDAO.saveVTRelation(vid,tool.getId());
        }
        for (Integer cid : cids) {

            toolDAO.saveCTRelation(cid,tool.getId());
        }


        for (Publications publications : publicationList) {
            Publications p =publicationDAO.findByDoi(publications.getDoi());
            if(null==p){
                p =publicationDAO.save(publications);

            }
            if(null==toolDAO.findPTRelation(p.getId(), tool.getId())){
                toolDAO.savePTRelation(p.getId(), tool.getId());
            }

            
        }

//        List<Credit> creditList =tool.getCredits();
        System.out.println(credits.size());
        for (int i = 0; i < credits.size(); i++) {

            Credit credit=credits.get(i);
            Organization org=null;
            Organization o =credit.getOrganization();
            if(null!=o){
                org =   organizationDAO.findByNameAndDepartmentAndCountry(o.getName(),o.getDepartment(),o.getCountry());
                if(null==org){
                    org= organizationDAO.save(o);

                    System.out.println("save org");
                }

                credit.setOrganization(org);
                credit.setTool(tool);

            }
            User user =null;
            User u =credit.getUser();

            if(u!=null){
                 user =userDAO.findByEmail(u.getEmail());
                if(null==user){
                   user = userService.createUser(user.getEmail(),u.getFullName(),false);

                }
                credit.setUser(user);
            }
            if(null==creditDAO.findByUserAndToolAndOrganization(user,tool,org)){
                credit.setCreatedAt(new Date());
                credit.setUpdatedAt(new Date());
                credit.setIdx(i);
            }else{
                credit.setId(creditDAO.findByUserAndToolAndOrganization(user,tool,org).getId());
                credit.setIdx(i);
                credit.setUpdatedAt(new Date());
            }

            creditDAO.save(credit);


        }
        return tool;
    }


    /**
     * 根据名称查找或创建
     * @param name
     * @return
     */

    public Tech  findOrCreatedTechByName(String name){
       Tech tech =techDAO.findByName(name);
       if(tech==null){
           tech =new Tech();
           tech.setName(name);
           tech =techDAO.save(tech);
       }
       return tech;
    }

    /**
     * 分页查询
     * @param page
     * @param size
     * @return
     */
    public  Page<Tool> findAllByPageable(int page, int size){
        PageRequest request = new PageRequest(page, size);
        Page<Tool> result = toolDAO.findAll( request);
        return  result;
    }
}
